﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Turnero.Entidades;

namespace Turnero.DAOs
{
    class DAO_Pacientes:BaseDatos
    {
        public Boolean existePaciente(String tipodoc, String nrodoc)
        {
            Boolean existe = false;

            SqlCommand cmd = new SqlCommand("paConsultarTipoNroDoc", cn);
            cmd.Connection = ConectarDB();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@tipodoc", tipodoc);
            cmd.Parameters.AddWithValue("@nrodoc", nrodoc);
            SqlDataReader dr = cmd.ExecuteReader();

            if(dr.Read())
            {
            existe = true;
            }
            return existe;
        }

        public DataTable damePacientesPorNombreYApellido(string ape, string nom)
        {
            ape = ape + "%";
            nom = nom + "%";
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("paConsultarPacientesApeNom", cn);
            cmd.Connection = ConectarDB();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@apellidos", ape);
            cmd.Parameters.AddWithValue("@nombres", nom);
            dt.Load(cmd.ExecuteReader());
            return dt;
        }

        public Boolean guardarPaciente(string apellidos, string nombres, 
                                       string tipodoc, string nrodoc, 
                                       DateTime fechanac, string sexo, 
                                       string dom, Int32 loc, string tel, 
                                       string cel, string email,  
                                       string obs)
        {   

            //try
            //{
                String query = "INSERT INTO tblPacientes (tipodocumento, nrodocumento, apellidos, nombres, "+
                               "fechanacimiento, calleynumero, localidad, sexo, telefono, celular, email, observaciones) "+
                               "values (@tipodoc, @nrodoc, @apellidos, @nombres, @fechanac, @dom, @loc, @sexo, @tel, @cel, "+
                               "@email, @obs)";

                SqlCommand cmd = new SqlCommand(query, ConectarDB());

                cmd.Parameters.AddWithValue("@tipodoc", tipodoc);
                cmd.Parameters.AddWithValue("@nrodoc", nrodoc);
                cmd.Parameters.AddWithValue("@apellidos", apellidos);
                cmd.Parameters.AddWithValue("@nombres", nombres);                
                cmd.Parameters.AddWithValue("@fechanac", fechanac);
                cmd.Parameters.AddWithValue("@dom", dom);             
                cmd.Parameters.AddWithValue("@loc", loc);
                cmd.Parameters.AddWithValue("@sexo", sexo);
                cmd.Parameters.AddWithValue("@tel", tel);
                cmd.Parameters.AddWithValue("@cel", cel);
                cmd.Parameters.AddWithValue("@email", email);
                
                cmd.Parameters.AddWithValue("@obs", obs);

                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                return true;
            //}
            //catch (Exception ex)
            //{
                //return false;
            //}          
        }

        public Boolean actualizarPaciente(string cod, string apellidos, string nombres,
                                       string tipodoc, string nrodoc,
                                       DateTime fechanac, string sexo,
                                       string dom, Int32 loc, string tel,
                                       string cel, string email, 
                                       string obs)
        {

            //try
            //{
                String query = "UPDATE tblPacientes " +
                               "SET tipodocumento = @tipodoc, nrodocumento = @nrodoc, apellidos = @apellidos, nombres = @nombres, " +
                               "fechanacimiento = @fechanac, calleynumero = @dom, localidad = @loc, sexo = @sexo, telefono = @tel, "+
                               "celular = @cel, email = @email, observaciones = @obs "+
                               "WHERE idpaciente = @cod";

                SqlCommand cmd = new SqlCommand(query, ConectarDB());

                cmd.Parameters.AddWithValue("@tipodoc", tipodoc);
                cmd.Parameters.AddWithValue("@nrodoc", nrodoc);
                cmd.Parameters.AddWithValue("@apellidos", apellidos);
                cmd.Parameters.AddWithValue("@nombres", nombres);
                cmd.Parameters.AddWithValue("@fechanac", fechanac);
                cmd.Parameters.AddWithValue("@dom", dom);
                cmd.Parameters.AddWithValue("@loc", loc);
                cmd.Parameters.AddWithValue("@sexo", sexo);
                cmd.Parameters.AddWithValue("@tel", tel);
                cmd.Parameters.AddWithValue("@cel", cel);
                cmd.Parameters.AddWithValue("@email", email);
                
                cmd.Parameters.AddWithValue("@obs", obs);
                cmd.Parameters.AddWithValue("@cod", cod);
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                return true;
            //}
            //catch (Exception ex)
            //{
               // return false;
            //}
        }

        public DataTable damePacientesPorTipoYNroDoc(string tipodoc, string nrodoc)
        {
            tipodoc = tipodoc + "%";
            nrodoc = nrodoc + "%";
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("paConsultarPacientesDocTipo", cn);
            cmd.Connection = ConectarDB();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@tipodoc", tipodoc);
            cmd.Parameters.AddWithValue("@nrodoc", nrodoc);
            dt.Load(cmd.ExecuteReader());
            return dt;
        }

        public bool BorrarPaciente(Int32 idpaciente)
        {
            try
            {
                String query = "DELETE from tblPacientes where idpaciente = @idpaciente";
                SqlCommand cmd = new SqlCommand(query, ConectarDB());

                cmd.Parameters.AddWithValue("@idpaciente", idpaciente);
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public DataTable dameTodosLosPacientes()
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("paConsultarTodosLosPacientes", cn);
            cmd.Connection = ConectarDB();
            cmd.CommandType = CommandType.StoredProcedure;
            dt.Load(cmd.ExecuteReader());
            return dt;
        }

        public String dameMaxID()
        {
            SqlCommand cmd = new SqlCommand("select max(idpaciente) from tblPacientes", cn);
            cmd.Connection = ConectarDB();
            cmd.CommandType = CommandType.Text;

            return cmd.ExecuteScalar().ToString();
        }

        public DataTable damePacientesPorTipoYNroDocExacto(string tipodoc, string nrodoc)
        {
            tipodoc = tipodoc + "%";
            
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("paConsultarPacientesDocTipoExacto", cn);
            cmd.Connection = ConectarDB();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@tipodoc", tipodoc);
            cmd.Parameters.AddWithValue("@nrodoc", nrodoc);
            dt.Load(cmd.ExecuteReader());
            return dt;
        }

    }
}

